
package database;

import core.COINCOMOSubSystem;
import core.COINCOMOSystem;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author Raed Shomali
 */
public class COINCOMOSystemManager
{
    private static String insertSystemSQL( String systemName )
    {
        StringBuffer sql = new StringBuffer();

        sql.append( "SELECT Insert_System(" );
        sql.append( "\'" + systemName + "\');" );

        return sql.toString();
    }

    public static COINCOMOSystem insertSystem()
    {
        COINCOMOSystem system = null;

        DBConnection connection = (DBConnection) COINCOMODatabaseManager.getConnection();

        // Insert Only When a Connection is available ..
        if ( connection != null )
        {
            try
            {
                system = new COINCOMOSystem();
                system.setName( "(System)" );

                // SQL Insert
                String sql = insertSystemSQL( system.getName() );

                // For Better Performance Insert Statements
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                // Do the insertion ...
                ResultSet rs = preparedStatement.executeQuery();

                try
                {
                    // Get Row
                    if ( rs.next() )
                    {
                        system.setUnitID( rs.getInt( 1 ) );
                    }
                }
                catch ( SQLException se )
                {
                    return null;
                }
                finally
                {
                    // Release from memory ...
                    preparedStatement.close();
                }
            }
            catch ( SQLException e )
            {
                return null;
            }
            finally
            {
                // Disconnect
                COINCOMODatabaseManager.disconnect( connection );
            }
        }

        return system;
    }

    public static boolean updateSystem( COINCOMOSystem system )
    {
        boolean isSuccessful = false;

        // If Exists
        if ( system != null )
        {
            DBConnection connection = (DBConnection) COINCOMODatabaseManager.getConnection();

            if ( connection != null )
            {
                try
                {
                    String sql = "update cocomo_systems_table set " +
                            "System_Name = ? " +
                            "where System_ID=?";

                    // Efficient Way of Updating
                    PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                    // Replace "?" With Respective Values ..
                    preparedStatement.setString( 1 , system.getName() );

                    preparedStatement.setLong( 2 , system.getUnitID() );

                    // Update
                    preparedStatement.executeUpdate();

                    // Free From Memory
                    preparedStatement.close();

                    // Update Status
                    isSuccessful = true;
                }
                catch ( SQLException e )
                {
                    // Print Any Problems ..
                    e.printStackTrace();
                }
                finally
                {
                    // Disconnect
                    COINCOMODatabaseManager.disconnect( connection );
                }
            }
        }

        return isSuccessful;
    }

    public static boolean deleteSystems( Vector<COINCOMOSystem> systems )
    {
        boolean isSuccessful = false;

        // If Exists
        if ( systems != null && !systems.isEmpty() )
        {
            DBConnection connection = (DBConnection) COINCOMODatabaseManager.getConnection();

            // Check if connection exists
            if ( connection != null )
            {
                try
                {
                    String sql = "delete from cocomo_systems_table where System_ID=?";

                    // Efficent Deletion
                    PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                    for ( int i = 0 ; i < systems.size() ; i++ )
                    {
                        COINCOMOSystem system = systems.get( i );

                        // Replace "?" With ID Value
                        preparedStatement.setLong( 1 , system.getUnitID() );

                        // Delete
                        preparedStatement.executeUpdate();
                    }

                    // Release From Memory
                    preparedStatement.close();

                    // Update Status
                    isSuccessful = true;
                }
                catch ( SQLException e )
                {
                    // Print any Problems ...
                    e.printStackTrace();
                }
                finally
                {
                    // Disconnect
                    COINCOMODatabaseManager.disconnect( connection );
                }
            }
        }

        return isSuccessful;
    }

    public static boolean loadSystem( COINCOMOSystem system , DBConnection connection )
    {
        // If no Connection ...
        if( connection == null )
        {
            // Create one ...
            connection = (DBConnection) COINCOMODatabaseManager.getConnection();
        }
        
        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                String sql = "select SubSystem_ID , SubSystem_Name from cocomo_SubSystems_table where system_ID = ?";

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                // Replace the "?" With the ID Value
                preparedStatement.setLong( 1 , system.getUnitID() );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                while ( rs != null && rs.next() )
                {
                    long subSystemID = rs.getLong( 1 );
                    String name = rs.getString( 2 );

                    // Create a Place Holder for Loaded Sub System ...
                    COINCOMOSubSystem subSystem = new COINCOMOSubSystem() ;
                    subSystem.setName( name );
                    subSystem.setUnitID( subSystemID );
                    subSystem.setParentID( system.getUnitID() );
                    
                    // Load Sub Systems Using the Same Connection For Efficiency ...
                    COINCOMOSubSystemManager.loadSubSystem( subSystem , connection ) ;
                    
                    system.addSubUnit( subSystem );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
                
                return false ;
            }
            finally
            {
                // Disconnect ..
                COINCOMODatabaseManager.disconnect( connection );
            }
        }

        return true ;
    }

    public static Vector<COINCOMOSystem> getAllSystems()
    {
        DBConnection connection = (DBConnection) COINCOMODatabaseManager.getConnection();

        Vector<COINCOMOSystem> systems = new Vector<COINCOMOSystem>();

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                String sql = "select System_ID , System_Name from cocomo_systems_table order by System_id";

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                ResultSet rs = preparedStatement.executeQuery();

                // Loop through ..
                while ( rs != null && rs.next() )
                {
                    long id = rs.getLong( 1 );
                    String name = rs.getString( 2 );

                    // Create a note
                    COINCOMOSystem system = new COINCOMOSystem();

                    // Set Note Parameters
                    system.setUnitID( id );
                    system.setName( name );

                    // Add to the List
                    systems.add( system );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
            finally
            {
                // Disconnect ..
                COINCOMODatabaseManager.disconnect( connection );
            }
        }

        return systems;
    }

}
